package ac.man.cs.afzal.tcm.sql_handler;

import java.io.*;
import java.sql.*;
import java.util.logging.*;

public class SQL_Handler
{

    public java.sql.Connection connection;
    public java.sql.Statement statement;
    
    private String database_url = "database_url";
    private String username = "username";
    private String password = "password";


    public void initialize_parameters(String database_url,String username, String password)
    {
        this.database_url = database_url;
        this.username = username;
        this.password = password;
    }

    public void connect_db()
    {

        //****************************DataBase Initiator****************************//
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(database_url, username, password);
        }
        catch (ClassNotFoundException cnfex)
        {
            System.err.println("Failed to load JDBC/ODBC driver.");
            cnfex.printStackTrace();
            System.exit(1);  // terminate program
        }
        catch (SQLException sqlex)
        {
            System.err.println("Unable to connect");
            sqlex.printStackTrace();
            System.exit(1);  // terminate program
        }
        catch (InstantiationException iex)
        {
            System.err.println("Unable to connect");
            iex.printStackTrace();
            System.exit(1);  // terminate program
        }
        catch (IllegalAccessException iex)
        {
            System.err.println("Unable to connect");
            iex.printStackTrace();
            System.exit(1);  // terminate program
        }
    }

    public void close_db()
    {
        try
        {
            connection.close();
        }
        catch (SQLException se)
        {
            System.out.println(se.getMessage());
        }
    }

    public void reset_auto_inc(String[] table_name)
    {
        try
        {
            for(int i = 0; i < table_name.length; i ++)
            {
                String query = "";
                query = "ALTER TABLE " + table_name[i] + " AUTO_INCREMENT = 1";

                statement = connection.createStatement();
                statement.executeUpdate(query);
                statement.close();
            }
        }
        catch (SQLException sqlex)
        {
            System.out.println("Value of SQL Exception is : " + sqlex.getMessage());
        }
    }

    public void delete_all(String[] table_name)
    {
        System.out.println("Deleting entries in table :" + table_name);
        try
        {
            for(int i = 0; i < table_name.length; i ++)
            {
                String query = "";
                query = "DELETE FROM " + table_name[i];

                statement = connection.createStatement();
                statement.executeUpdate(query);
                statement.close();
            }
        }
        catch (SQLException sqlex)
        {
            System.out.println("Value of SQL Exception is : " + sqlex.getMessage());
        }
    }

    public void delete(String table_name, String[] fields, String[] values)
    {
        int deleteCount;
        int no_of_columns = fields.length;

        for(int i = 0; i < no_of_columns; i ++)
        {
            try
            {
                String query = "DELETE FROM " + table_name + " WHERE " + fields[i] + "='" + values[i] + "'";
                statement = connection.createStatement();
                deleteCount = statement.executeUpdate(query);
                System.out.println("No of rows deleted = " + deleteCount);
                statement.close();
            }
            catch (SQLException sqlex)
            {
                System.out.println("Value of SQL Exception is : " + sqlex.getMessage());
            }
        }
    }

    /**
     * Resets the given table name
     * @param table_name
     */
    public void reset_tables(String[] table_name)
    {
        try
        {
            String input;
            BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
            System.out.print("Do you want to delete previous entries in following tables? : ");
            print_table_names(table_name);
            System.out.println("Enter 'y' for yes, 'n' for no, or 'e' to exit : ");
            input = in.readLine();
            input = input.trim();
            if(input.equals("y"))
            {
                delete_all(table_name);
                reset_auto_inc(table_name);
            }
            else if(input.equals("e"))
            {
                System.exit(1);
            }
            else
            {
                System.out.println("Do Nothing: No Databases Reset");
            }
        }
        catch (IOException ex)
        {
            Logger.getLogger(SQL_Handler.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void print_table_names(String[] tables)
    {
        System.out.println("Entries in following tables shall be deleted: ");

        for(int i = 0; i < tables.length; i++)
        {
            System.out.println(tables[i]);
        }
    }

    public void create_table(String table_name, String[] fields, String[] types)
    {
        int no_of_columns = fields.length;
        if(fields.length == 1)
        {
            try
            {
                statement = connection.createStatement();
                String query = "CREATE TABLE " + table_name + "(" + fields[0] + " " + types[0] + ")";
                statement.executeUpdate(query);
            }
            catch (SQLException sqlex)
            {
                System.out.println("Value of SQL Exception is : " + sqlex.getMessage());
            }
        }
        else
        {
            try
            {
                statement = connection.createStatement();
                String query = "CREATE TABLE " + table_name + "(" + fields[0] + " " + types[0] + ")";
                statement.executeUpdate(query);
            }
            catch (SQLException sqlex)
            {
                System.out.println("Value of SQL Exception is : " + sqlex.getMessage());
            }
            for(int i = 1; i < no_of_columns; i ++)
            {
                try
                {
                    statement = connection.createStatement();
                    String query = "ALTER TABLE " + table_name + " ADD COLUMN " + fields[i] + " " + types[i];

                    statement.executeUpdate(query);
                }
                catch (SQLException sqlex)
                {
                    System.out.println("Value of SQL Exception is : " + sqlex.getMessage());
                }
            }
        }
    }

    public void drop_table(String table_name)
    {
        try
        {
            statement = connection.createStatement();
            String query = "DROP TABLE " + table_name;
            statement.executeUpdate(query);
        }
        catch (SQLException sqlex)
        {
            System.out.println("Value of SQL Exception is : " + sqlex.getMessage());
        }
    }

    public void update_table(String table_name, int id, String column, String value)
    {
        try
        {
            Statement stmt = connection.createStatement();
            String query = "UPDATE " + table_name + " SET " + column + " = " + value + " WHERE ID = " + id;
            stmt.executeUpdate(query);
        }
        catch (SQLException e)
        {
        }
    }

    public void insert_table(String table_name, String[] fields, String[] values)
    {
        int no_of_columns = fields.length;

        switch(no_of_columns)
        {
            case 1:
                try
                {
                    statement = connection.createStatement();
                    String query = "INSERT INTO " + table_name + " (" + fields[0] + ") VALUES ('" + values[0] + "')";
                    statement.executeUpdate(query);
                }
                catch (SQLException sqlex)
                {
                    System.out.println("SQL Exception Inserting : " + sqlex.getMessage());
                }
                break;

            case 2:
                try
                {
                    if(values[1].equals("NaN"))
                    {
                        values[1] = "1";
                    }


                    statement = connection.createStatement();
                    String query = "INSERT INTO " + table_name + " (" + fields[0] + "," + fields[1] + ") VALUES ('" + values[0] + "'" + ", '" + values[1] + "')";
                    statement.executeUpdate(query);
                }
                catch (SQLException sqlex)
                {
                    System.out.println("SQL Exception Inserting : " + sqlex.getMessage());
                }
                break;

            case 3:
                try
                {
                    statement = connection.createStatement();
                    String query = "INSERT INTO " + table_name + " (" + fields[0] + "," + fields[1] + "," + fields[2] + ") VALUES ('" + values[0] + "'" + ", '" + values[1] + "'" + ", '" + values[2] +
                            "')";
                    statement.executeUpdate(query);
                }
                catch (SQLException sqlex)
                {
                    System.out.println("SQL Exception Inserting : " + sqlex.getMessage());
                }
                break;

            case 4:
                try
                {
                    statement = connection.createStatement();
                    String query = "INSERT INTO " + table_name + " (" + fields[0] + "," + fields[1] + "," + fields[2] + "," + fields[3] + ") VALUES ('" + values[0] + "'" + ", '" + values[1] + "'" + ", '" + values[2] + "'" + ", '" + values[3] +
                            "')";
                    statement.executeUpdate(query);
                }
                catch (SQLException sqlex)
                {
                    System.out.println("SQL Exception Inserting : " + sqlex.getMessage());
                }
                break;

            case 5:
                try
                {
                    statement = connection.createStatement();
                    String query = "INSERT INTO " + table_name + " (" + fields[0] + "," + fields[1] + "," + fields[2] + "," + fields[3] + "," + fields[4] + ") VALUES ('" + values[0] + "'" + ", '" + values[1] + "'" + ", '" + values[2] + "'" + ", '" + values[3] + "'" + ", '" + values[4] +
                            "')";
                    statement.executeUpdate(query);
                }
                catch (SQLException sqlex)
                {
                    System.out.println("SQL Exception Inserting : " + sqlex.getMessage());
                }
                break;

            case 6:
                try
                {
                    statement = connection.createStatement();
                    String query = "INSERT INTO " + table_name + " (" + fields[0] + "," + fields[1] + "," + fields[2] + "," + fields[3] + "," + fields[4] + "," + fields[5] + ") VALUES ('" + values[0] + "'" + ", '" + values[1] + "'" + ", '" + values[2] + "'" + ", '" + values[3] + "'" + ", '" + values[4] + "'" + ", '" + values[5] +
                            "')";
                    statement.executeUpdate(query);
                }
                catch (SQLException sqlex)
                {
                    System.out.println("SQL Exception Inserting No 6: " + sqlex.getMessage());
                }
                break;

            case 7:
                try
                {
                    statement = connection.createStatement();
                    String query = "INSERT INTO " + table_name + " (" + fields[0] + "," + fields[1] + "," + fields[2] + "," + fields[3] + "," + fields[4] + "," + fields[5] + "," + fields[6] + ") VALUES ('" + values[0] + "'" + ", '" + values[1] + "'" + ", '" + values[2] + "'" + ", '" + values[3] + "'" + ", '" + values[4] + "'" + ", '" + values[5] + "'" + ", '" + values[6] +
                            "')";
                    statement.executeUpdate(query);
                }
                catch (SQLException sqlex)
                {
                    System.out.println("SQL Exception Inserting No 7: " + sqlex.getMessage());
                }
                break;

            case 8:
                try
                {
                    statement = connection.createStatement();
                    String query = "INSERT INTO " + table_name + " (" + fields[0] + "," + fields[1] + "," + fields[2] + "," + fields[3] + "," + fields[4] + "," + fields[5] + "," + fields[6] + "," + fields[7] + ") VALUES ('" + values[0] + "'" + ", '" + values[1] + "'" + ", '" + values[2] + "'" + ", '" + values[3] + "'" + ", '" + values[4] + "'" + ", '" + values[5] + "'" + ", '" + values[6] + "'" + ", '" + values[7] +
                            "')";
                    statement.executeUpdate(query);
                }
                catch (SQLException sqlex)
                {
                    System.out.println("SQL Exception Inserting No 8: " + sqlex.getMessage());
                }
                break;

            default:
                System.out.println("Insert Statement not executed correctlly");
        }
    }
}
